package com.example.administrator.activity.parcitise.Storage.SQList;

import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.util.Log;
import android.view.View;
import android.widget.Button;
import android.widget.Toast;

import com.example.administrator.activity.R;

public class SQLiteActivity extends AppCompatActivity {
    Button btn_insert, btn_select_, btn_delete, btn_update, btn_trans;
    Myheleper myheleper;
    SQLiteDatabase db;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_sqlite);
        init();
        myheleper = new Myheleper(this, "my3.db", null, 3);
        db = myheleper.getWritableDatabase();
        View.OnClickListener clickListener = new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                String sql = "";
                switch (v.getId()) {
                    case R.id.ch3_sqlite_btn_insert:
                        sql = "insert into user(name,sex) values(?,?)";
                        db.execSQL(sql, new Object[]{"张三", "男"});

//                        ContentValues cv=new ContentValues();
//                        cv.put("name","lisi");
//                        cv.put("sex","male");
//                        db.insert("user",null,cv);

                        Toast.makeText(SQLiteActivity.this, "添加成功", Toast.LENGTH_SHORT).show();
                        break;
                    case R.id.ch3_sqlite_btn_delete:
                        sql = "delete from user where name=?";
                        db.execSQL(sql, new Object[]{"张三"});
                        db.execSQL(sql,new Object[]{"slsher"});

//                        db.delete("user","id=? and name=?",new String[]{3+"","zhangsan"});

                        Toast.makeText(SQLiteActivity.this, "删除成功", Toast.LENGTH_SHORT).show();
                        break;
                    case R.id.ch3_sqlite_btn_update:
                        sql = "update user set sex=? where name=?";
                        db.execSQL(sql, new Object[]{"女", "张三"});

//                        ContentValues contentValues=new ContentValues();
//                        db.update("user",contentValues,"id=",new String[]{4+""});
                        Toast.makeText(SQLiteActivity.this, "修改成功", Toast.LENGTH_SHORT).show();
                        break;
                    case R.id.ch3_sqlite_btn_select:
                        sql = "select * from user";
                        Cursor cursor = db.rawQuery(sql, null);
                        while (cursor.moveToNext()) {
                            String name = cursor.getString(cursor.getColumnIndex("name"));
                            String sex = cursor.getString(cursor.getColumnIndex("sex"));
                            Toast.makeText(SQLiteActivity.this, name + ":" + sex, Toast.LENGTH_SHORT).show();
                        }

//                        Cursor cursor = db.query("user", new String[]{"name","sex"},null, null, null, null, null, null);
//                        while (cursor.moveToNext()) {
//                            String name = cursor.getString(cursor.getColumnIndex("name"));
//                            String sex = cursor.getString(cursor.getColumnIndex("sex"));
//                            Toast.makeText(SQLiteActivity.this, name + ":" + sex, Toast.LENGTH_SHORT).show();
//                        }
                        break;
                    case R.id.ch3_sqlite_btn_trans:
                        /**
                         * 事务中包含若干条SQl语句，这些语句要么全部执行成功，要么全部不执行
                         * 操作：1)开启事务
                         * 2）执行若干条SQL语句
                         * （若执行过程有异常，会自动回滚事务之前的状态）
                         * 3）提交事务
                         */
//                        开启事务
                        db.beginTransaction();
                        String sql1 = "update user set name=?";
                        String sql2 = "update user set sex=?";
                        try {
                            db.execSQL(sql1, new Object[]{"slsher"});
                            System.exit(0);
                            db.execSQL(sql2, new Object[]{"cartoon"});
                            db.setTransactionSuccessful();
                        } catch (Exception e) {
                            Log.v("trans", e.getMessage());
                        } finally {
                            db.endTransaction();
                        }
                        break;
                }
            }
        };
        btn_insert.setOnClickListener(clickListener);
        btn_delete.setOnClickListener(clickListener);
        btn_update.setOnClickListener(clickListener);
        btn_select_.setOnClickListener(clickListener);
        btn_trans.setOnClickListener(clickListener);
    }

    private void init() {
        btn_insert = findViewById(R.id.ch3_sqlite_btn_insert);
        btn_select_ = findViewById(R.id.ch3_sqlite_btn_select);
        btn_delete = findViewById(R.id.ch3_sqlite_btn_delete);
        btn_update = findViewById(R.id.ch3_sqlite_btn_update);
        btn_trans = findViewById(R.id.ch3_sqlite_btn_trans);

    }
}
